from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter


def write_to_excel(data,out_file="房产数据.xlsx"):
    """
    Write data to an Excel file.

    :param out_file: Path to the Excel file.
    :param data: List of lists containing the data to write.
    """
    # Create a new workbook and select the active worksheet
    wb = Workbook()
    ws = wb.active

    row_title = ["编号", "标题", "小区名称", "总价（万元）", "均价", "户型", "面积", "楼层", "朝向", "建筑年限", "地址", "链接"]
    for col_num, title in enumerate(row_title, 1):
        cell = ws.cell(row=1, column=col_num, value=title)
        # 设置标题样式
        cell.font = Font(bold=True, color="FFFFFF", size=12)
        cell.fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = Border(left=Side(style="thin"), right=Side(style="thin"),
                             top=Side(style="thin"), bottom=Side(style="thin"))
    # 写入数据行
    for row_num, item in enumerate(data, 2):
        ws.cell(row=row_num, column=1, value=row_num - 1)  # 编号
        ws.cell(row=row_num, column=2, value=item["tittle"])
        ws.cell(row=row_num, column=3, value=item["community_name"])
        ws.cell(row=row_num, column=4, value=float(item["total_price"]))
        ws.cell(row=row_num, column=5, value=float(item["avg_price"]))
        ws.cell(row=row_num, column=6, value=item["house_type"])
        ws.cell(row=row_num, column=7, value=item["house_area"])
        ws.cell(row=row_num, column=8, value=item["house_floor"])
        ws.cell(row=row_num, column=9, value=item["house_towards"])
        ws.cell(row=row_num, column=10, value=item["house_year"])
        ws.cell(row=row_num, column=11, value=item["address"])
        ws.cell(row=row_num, column=12, value=item["house_link"])

        # 为链接添加超链接
        ws.cell(row=row_num, column=12).hyperlink = item["house_link"]
        ws.cell(row=row_num, column=12).style = "Hyperlink"
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        column_letter = get_column_letter(col[0].column)

        for cell in col:
            try:
                value_length = len(str(cell.value))
                if cell.font.bold:
                    value_length *= 1.2
                if value_length > max_length:
                    max_length = value_length
            except Exception as e:
                pass

        adjusted_width = (max_length + 2)
        ws.column_dimensions[column_letter].width = adjusted_width

    # 冻结标题行
    ws.freeze_panes = "A2"

    # 保存文件
    wb.save(out_file)
    print(f"Excel文件已生成：{out_file}.xlsx")

if __name__ == "__main__":
    # Define the file path and data to write
    file_path = "安居客房价信息.xlsx"


    data = [
        {"title":"Example House","community_name":"Example Community"},
        {"title":"Example House","community_name":"Example Community"},
        {"title":"Example House","community_name":"Example Community"},
    ]

    for num,item in enumerate(data,2):
        print(num,item)
